import openpyxl

#导入工作簿
wb=openpyxl.load_workbook('D:\GITEE\study_code\example.xlsx')

#Getting sheets from the workbook
#打印每一个表单的名字
for sheet in wb:
    print(sheet.title)

#创建新表单，注意创建后要保存才行
mySheet=wb.create_sheet('mySheet')
print(wb.sheetnames)

#得到表单
#sheet4=wb['mySheet']

#Getting cells from the sheets
ws=wb.active#Getting the active sheet
print(ws)
print(ws['A1'])#单元格的引用方式1
print(ws['A1'].value)

c=ws['B1']
print(f'Row {c.row} ,Colume {c.column} is {c.value}')
print(f'Cell {c.coordinate} is {c.value}')

#注意excel的行列是从1开始的
print(ws.cell(row=1,column=2))#单元格的引用方式2
print(ws.cell(row=1,column=2).value)
for i in range(1,8,2):
    print(i,ws.cell(row=i,column=2).value)

#Getting rows and columns from the sheets
colC=ws['C']#得到单元格列变量
print(colC)
print(colC[2].value)#得到某一处的单元格，索引从0开始，但行列标从1开始
row6=ws[6]#得到单元格行变量
#利用切片方式得到单元格行列变量
col_range=ws['B:C']
row_range=ws[2:6]
print(col_range)

for col in col_range:
    for cell in col:
        print(cell.value)

for row in row_range:
    for cell in row :
        print(cell.value)

#限定行范围方式1
for row in ws.iter_rows(min_row=1,max_row=4,min_col=1,max_col=4):
    for cell in row:
        print(f'{cell.value}\t',end='')
    print('\n')

#print(tuple(ws.rows)) #这个是元组的元组

#限定行范围方式2
cell_range=ws['A1:B2']
for rowOfCellObject in cell_range:
    for cellObj in rowOfCellObject:
        print(cellObj.value,end='\t')
    print('')

#得到该表单的行数和列数
print('{}*{}'.format(ws.max_row,ws.max_column))

#获取列数值的对应关系
from openpyxl.utils import get_column_letter,column_index_from_string
print(get_column_letter(2),get_column_letter(900))
print(column_index_from_string('AAH'))